﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Text.RegularExpressions;
namespace FTchina
{
    public class OracalDB
    {
        public static string connstr = System.Configuration.ConfigurationManager.AppSettings["oConnectionString"];

        protected static OracleConnection conn = new OracleConnection(connstr);
        protected static OracleCommand comm = new OracleCommand();

        /// <summary> 
        /// 打开数据库连接 
        /// </summary> 
        public static void OpenConnection()
        {
            CheckConnection();
        }
        public static bool CheckConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                try
                {

                    conn.ConnectionString = connstr;
                    comm.Connection = conn;
                    conn.Open();
                }
                catch
                {


                    return false;
                }
            }
            return true;
        }
        /// <summary> 
        /// 关闭当前数据库连接 
        /// </summary> 
        public static void CloseConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
                comm.Dispose();
            }
        }






        /// <summary>
        /// 执行Oracle语句，返回影响的记录数
        /// </summary>
        /// <param name="OracleString">Oracle语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteOracle(string OracleString)
        {
            using (OracleConnection connection = new OracleConnection(connstr))
            {
                using (OracleCommand cmd = new OracleCommand(OracleString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (Exception E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }






        /// <summary> 
        /// 执行Sql查询语句 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public static bool ExecuteSql(string sqlstr)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connstr))
                {
                    using (OracleCommand cmd = new OracleCommand(sqlstr, connection))
                    {
                        cmd.ExecuteNonQuery();
 
                        return true;
                    }
                }
            }
            catch (System.Exception er)
            {
                // LogNet.LogBLL.error(sqlstr + "  " + er.Message);

                return false;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary> 
        /// 返回指定Sql语句的DataTable 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public static DataTable GetDataTable(string sqlstr)
        {
            return GetDataSet(sqlstr).Tables[0];

        }
        /// <summary> 
        /// 返回指定Sql语句的DataTable，带缓存
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <param name="cache">cache标志</param> 
        /// <returns>DataTable</returns> 
        public static DataTable GetDataTable(string sqlstr, string cache)
        {

            if (cache.Length != 0)
            {
                if (FTchina.Caching.FTchinaCache.IsExits(cache + sqlstr))
                {
                    return (DataTable)HttpRuntime.Cache.Get(cache + sqlstr);
                }
                else
                {
                    DataTable dt = GetDataSet(sqlstr).Tables[0];


 
                    return dt;
                }

            }
            else
            {
                return GetDataSet(sqlstr).Tables[0];
            }
        }





        /// <summary>
        /// 在事务中处理大量数据
        /// </summary>
        /// <param name="sqls"></param>
        public static Boolean ExecuteSqlTransaction(ArrayList sqls)
        {

            using (OracleConnection connection = new OracleConnection(connstr))
            {
                using (OracleCommand comm = new OracleCommand())
                {


                    OracleTransaction transaction = connection.BeginTransaction();
                    comm.Transaction = transaction;
                    try
                    {

                        for (int i = 0; i < sqls.Count; i++)
                        {
                            comm.CommandType = CommandType.Text;
                            comm.CommandText = sqls[i].ToString();

                            comm.ExecuteNonQuery();

 
                        }
                        transaction.Commit();
                        return true;

                    }
                    catch (System.Exception er)
                    {
 
                        transaction.Rollback();
                        return false;
                    }
                    finally
                    {
                        CloseConnection();
                    }
                }
            }
        }


        /// <summary> 
        /// 返回指定Sql语句的DataSet
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public static DataSet GetDataSet(string sqlstr)
        {

            OracleDataAdapter da = new OracleDataAdapter();
            DataSet dataset = new DataSet();
            try
            {
                using (OracleConnection connection = new OracleConnection(connstr))
                {
                    using (OracleCommand cmd = new OracleCommand(sqlstr, connection))
                    {
                        {
                            da.SelectCommand = cmd;
                            da.Fill(dataset);
                         }
                    }
                }
            }
            catch (System.Exception er)
            {
             }
            finally
            {
                CloseConnection();
            }
            return dataset;
        }

        //检测数据是否存在，返回真假
        public static bool testread(string sqlstr)
        {
            bool flag = true;
            DataSet ds = GetDataSet(sqlstr);
            if (ds.Tables.Count == 0) { flag = false; }
            else
            {
                if (ds.Tables[0].Rows.Count > 0) flag = true;
                else flag = false;
            }
            return flag;
        }

        /// <summary>
        /// 返回特定值的查询结果，带缓存
        /// </summary>
        /// <param name="wKey">列名称 如 "select com from table where id=3 "中的com</param>
        /// <param name="fTable">表名称 如 "select com from table"中的table</param>
        /// <param name="wStr">限制条件 如 "select com from table where id=3 "中的id=3</param>
        /// <param name="Cache">Cache标志</param>
        /// <returns></returns>
        public static string GetValueByKey(string wKey, string fTable, string wStr, string Cache)
        {
            string tempwords = string.Empty;

            if (Cache.Length != 0)
            {
                if (FTchina.Caching.FTchinaCache.IsExits(Cache + wKey + fTable + wStr))
                {
                    tempwords = FTchina.Caching.FTchinaCache.GetCache(Cache + wKey + fTable + wStr);
                }
                else
                {
                    try
                    {
                        tempwords = GetDataTable("select " + wKey + " from " + fTable + " where " + wStr).Rows[0][0].ToString();
                        FTchina.Caching.FTchinaCache.AddCache(Cache + wKey + fTable + wStr, tempwords);
                    }
                    catch
                    {

                        tempwords = "";
                    }
                }

            }
            else
            {
                try
                {
                    tempwords = GetDataTable("select " + wKey + " from " + fTable + " where " + wStr).Rows[0][0].ToString();
                }
                catch
                {

                    tempwords = "";
                }

            }
            return tempwords;
        }
        /// <summary>
        /// 返回特定值的查询结果
        /// </summary>
        /// <param name="wKey">列名称 如 "select com from table where id=3 "中的com</param>
        /// <param name="fTable">表名称 如 "select com from table"中的table</param>
        /// <param name="wStr">限制条件 如 "select com from table where id=3 "中的id=3</param>
        /// <returns></returns>
        public static string GetValueByKey(string wKey, string fTable, string wStr)
        {
            return GetDataTable("select " + wKey + " from " + fTable + " where " + wStr).Rows[0][0].ToString();
        }
        #region 分页的方法page为当前页码，NUMCount为每页数据
        public static DataSet ShowForPage(string table, string Where, int curragePage, int NUMCount)
        {
            int number = 0;
            if (curragePage > 1)
            {
                number = NUMCount * (curragePage - 1);
            }

            //  string strsql = "SELECT TOP " + NUMCount + " * FROM " + table + " WHERE id NOT IN (SELECT TOP " + number + " id FROM " + table + " ORDER BY id desc) and (" + Where + ") ORDER BY id desc";
            string strsql = "SELECT TOP " + NUMCount + " * FROM " + table + " WHERE id NOT IN (SELECT TOP " + number + " id FROM " + table + " where (" + Where + ") ORDER BY id desc) and (" + Where + ") ORDER BY id desc";
            return GetDataSet(strsql);
        }
        #endregion



        public static DataSet ExecuteSqlForDataSetPageing(string strSQL, int startindex, int pagesize, string tablename)
        {


            OracleDataAdapter da = new OracleDataAdapter();
            DataSet dataset = new DataSet();
            try
            {
                OpenConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = strSQL;
                da.SelectCommand = comm;
                da.Fill(dataset, startindex, pagesize, tablename);
             }
            catch (System.Exception er)
            {
             }
            finally
            {
                CloseConnection();
            }
            return dataset;
        }
    }
}
